﻿
/*
 * 功    能：记录数据操作（INSERT、UPDATE、DELETE）日志(测试版)【自动创建触发器】V1.0
 * 参数说明：
	一、输入参数：
	1、@TableList：表名列表（多个用逗号分割）。
	2、@OperationUserColumn：记录表的操作用户的列名（没有可提供空字符串）。
	3、@PrimaryKeyColumn：表的主键列名。
	二、输出参数：
	1、@DorpTriggerStr：提示信息。
 * 作    者：胡勇
 * 创建时间：2012-03-20
 * 版    本：V1.0测试版
 * 修改记录：
 *		  1、2012-03-22 胡勇 增加对操作主键的记录。
 */
CREATE PROCEDURE [dbo].[RDIFramework_AutoCreateOperationLogTrigger]
(
    @TableList NVARCHAR(max),--表名列表
    @OperationUserColumn NVARCHAR(50) = '', --记录操作用户的列(主要用于对当前表中有用户字段的情形)
    @PrimaryKeyColumn NVARCHAR(50),--主键列名
    @DorpTriggerStr NVARCHAR(max) OUTPUT
)
As

DECLARE @Sql NVARCHAR(max),
        @str NVARCHAR(max),
        @ObjectName NVARCHAR(128),
		@UserColumn NVARCHAR(50)
SET @Str=N'
/*
 * 功    能：记录数据操作日志(INSERT、UPDATE、DELETE)
 * 作    者：胡勇
 * 编写日期：''%CreateDate%''
 * 版    本: V1.0
 * 修改记录:
 */
CREATE TRIGGER tr_%ObjectName%_OperationLog
    ON %ObjectName%
    AFTER INSERT,UPDATE,DELETE
AS
	SET NOCOUNT ON
    DECLARE @Data NVARCHAR(Max),
			@Type CHAR(6),
			@Table NVARCHAR(128),
			@Cols NVARCHAR(max),
			@Sql NVARCHAR(max),
			@UserName NVARCHAR(50),
			@PrimaryKeyColumn NVARCHAR(30) --主键列名
			
    /*提取表字段內容,這裡不做數據類型判斷*/
    
	SELECT  
			@PrimaryKeyColumn = ''%PrimaryKeyColumn%'',
			@UserName= ''%UserName%'',
			@Table=''%ObjectName%'',
            @Data='''',
            @Cols=''''
            
    SELECT @Cols=+@Cols+'''''' ''+name+''=''''+Convert(nvarchar(max),Isnull(Quotename(''+name+'',''''"''''),''''null''''))+''
    FROM sys.columns 
    WHERE object_id=object_id(@Table) 
    SET @Cols=LEFT(@Cols,len(@Cols)-1)
    
    
    /*判斷操作類型，Insert/Update/Delete*/
    
    IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
        SET @Type=''Insert''
    ELSE If EXISTS(SELECT 1 FROM Inserted) AND EXISTS(SELECT 1 FROM deleted)
        SET @Type=''Update''
    ELSE
        SET @Type=''Delete''

    /*写入日志表*/
    Begin
        --读更新前后的数据
        If OBJECT_ID(''tempdb..#TmpTrigger1'') IS NOT NULL
                Drop table #TmpTrigger1
                
        --更新前的数据TriggerKeyFlag=0、更新后的数据 TriggerKeyFlag=1
        
        SELECT *,TriggerKeyFlag=0 INTO #TmpTrigger1 FROM deleted 
        UNION ALL
        SELECT *,TriggerKeyFlag=1 FROM inserted
        
        --插入主表记录        		
		--SET @Sql=N''INSERT INTO DbOperationLog(Operator,Note) VALUES(''+@UserName+'',''+@Type+'')''
		--EXEC sp_executesql @Sql	
		SET @Sql=N''INSERT INTO DbOperationLog(Operator,Note)
				    SELECT TOP 1 ''+@UserName+'',@Type FROM #TmpTrigger1''
		EXEC sp_executesql @Sql,N''@Type CHAR(6)'',@Type
		
	
		
        /* 構造SQL語句,暫時不考慮以下情況：        
        
            1.這裡不考慮幷發的處理，所以取對應日誌主表(DbOperationLog)的ID時候，讀的是最新的ID,
            在目前環境中，幷發的可能性很小，要是以後應用于幷發環境，需要重新修改這一位置
            
            2.當日誌表在獨立一個庫時候，以下的語句不適用.
        */
        SET @Sql=N''INSERT INTO DbOperationDetailLog (DbOperationLogID,TableName,TablePrimaryKey,Description,OperationType,Flag) 
                        SELECT (SELECT TOP(1) ID FROM DbOperationLog ORDER BY Logdate DESC),
                                @table,''+@PrimaryKeyColumn+'',''+@Cols+'',@Type,TriggerKeyFlag
                        FROM #TmpTrigger1''
        --執行SQL語句                                                        
        EXEC sp_executesql @Sql,N''@table nvarchar(128),@Type nvarchar(max)'',@table,@Type
    End
'

SET @TableList=@TableList+','
SET @DorpTriggerStr=''

WHILE @TableList>'' /*根据提供的Table列表，创建对应Table的Trigger*/
BEGIN

    SET @ObjectName=substring(@TableList,1,Charindex(N',',@TableList)-1)
    --SET @User = @OperationUserName

    IF @ObjectName>''
    BEGIN    
        /*构造删除Trigger語句，若存在就删除，以防止构造触发器时发生错误*/
        SET @DorpTriggerStr=@DorpTriggerStr+Char(13)+Char(10)+'If object_id(''tr_'+@ObjectName+'_temp'') Is Not Null Drop Trigger tr_'+@ObjectName+'_temp'
        SET @Sql = Replace(@Str,'%UserName%',@OperationUserColumn)
        SET @Sql = Replace(@Sql,'%ObjectName%',@ObjectName)        
        SET @Sql = Replace(@Sql,'%PrimaryKeyColumn%',@PrimaryKeyColumn)  
        SET @Sql = Replace(@Sql,'%CreateDate%',CONVERT (date, SYSDATETIME()))  
                
        /*先刪除之前创建的Trigger語句,以防发生错误*/
        EXEC('If object_id(''tr_'+@ObjectName+'_temp'') Is Not Null Drop Trigger tr_'+@ObjectName+'_temp')
        --PRINT @Sql
        /*创建 Trigger*/
        EXEC(@Sql)
    END
    
    SET @TableList=stuff(@TableList,1,Charindex(N',',@TableList),'')
END

GOTO SubExit

ErrorExit:

--错误处理Drop Trigger
If @DorpTriggerStr>''
    EXEC(@DorpTriggerStr)

SET @DorpTriggerStr=''

SubExit:









